package com.huicai.poi.utils;

import com.huicai.poi.entity.Product;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.*;

/**
 * @author: MR.LIU
 * @description: 读取文件
 * @date: 2020/6/7
 * @time: 17:19
 */
public class ExcelReadUtil {
    private static final Logger logger = LoggerFactory.getLogger(ExcelReadUtil.class);

    public static Map<String, Object> getExcelData(MultipartFile file, String realPath, String year) {
        Workbook wb = null;
        FileInputStream in = null;
        File remoteFile = null;
        Map<String, Object> readResult = null;
        try {
            String oldName = file.getOriginalFilename();
            //获取文件后缀
            String postfix = getPostfix(oldName);
            if (StringUtils.isBlank(postfix)) {
                return null;
            }
            // 得到新名字
            String newName = StrUtils.createFileNameUseTime() + "." + postfix;
            // 得到文件夹名(按日期管理)
            String folderName = StrUtils.createDirUseCurrentDate();
            // 根据文件夹名构造新的文件夹
            File folder = new File(realPath + "/" + folderName);
            if (!folder.exists()) {
                folder.mkdirs();
            }
            // 在服务器的upload路径下构造一个文件
            String folderpath = folder.getAbsolutePath() + "/" + newName;
            remoteFile = new File(folderpath);
            // 将上传的文件写入新建的文件中
            file.transferTo(remoteFile);
            // 根据新建的文件实例化输入流
            in = new FileInputStream(remoteFile);
            //读取文件内容
            if (isExcel2003(oldName)) {
                wb = new HSSFWorkbook(in);
                readResult = readExcelValue(wb, oldName, year);
            } else if (isExcel2007(oldName)) {
                wb = new XSSFWorkbook(in);
                readResult = readExcelValue(wb, oldName, year);
            } else {
                readResult = new HashMap<>();
                readResult.put("list", new ArrayList<>());
                readResult.put("msg", "文件类型错误");
            }
        } catch (IOException e) {
            readResult = new HashMap<>();
            readResult.put("list", new ArrayList<>());
            readResult.put("msg", "文件类型错误");
            e.printStackTrace();
            return readResult;
        } finally {
            if (in != null) {
                try {
                    in.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            //删除导入文件
            if (remoteFile.exists()) {
                remoteFile.delete();
            }
        }
        return readResult;

    }

    /**
     * 读取文件
     *
     * @param wb
     * @param oldName
     * @param year
     * @return
     */
    private static Map<String, Object> readExcelValue(Workbook wb, String oldName, String year) {
        List<Product> list = new LinkedList<>();
        //月
        String month = getMonth(getPrefix(oldName));
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        String errorMsg = "";
        try {
            //总行数
            int totoalRows = 0;
            // 总列数
            int totalCells = 0;
            //读取表
            loop:
            for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
                Sheet sheet = wb.getSheetAt(sheetIndex);
                if (sheet == null) {
                    continue;
                }
                //获取sheet页名称
                // String sheetName = sheet.getSheetName();
                //日期
                // String date = getDate(sheetName);
                // 得到Excel sheet页的行数 totoalRows = sheet.getLastRowNum();
                totoalRows = sheet.getPhysicalNumberOfRows();
                // 得到Excel sheet页的列数(前提是有行数)，从第二行算起
                if (totoalRows >= 2 && sheet.getRow(1) != null) {
                    // totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
                    totalCells = 7;
                }
                // 循环Excel sheet页的行数,从第二行开始。标题不入库
                for (int rowIndex = 1; rowIndex < totoalRows; rowIndex++) {
                    Row row = sheet.getRow(rowIndex);
                    if (row == null) {
                        errorMsg += "第" + (sheetIndex + 1) + "sheet页,第" + (rowIndex + 1) + "行,数据为空,请仔细检查！\r\n";
                        continue;
                    }
                    Product product = new Product();
                    List<String> stringList = new LinkedList<>();
                    // 循环Excel的列  从第二列开始导入
                    for (int cellIndex = 1; cellIndex < totalCells; cellIndex++) {
                        Cell cell = row.getCell(cellIndex);
                        if (null != cell && !("".equals(cell))) {
                            switch (cellIndex) {
                                case 1:
                                    String cell1 = cell.getStringCellValue();
                                    if (StringUtils.isNotBlank(cell1)) {
                                        product.setProductName(cell1);
                                    } else {
                                        errorMsg += "第" + (sheetIndex + 1) + "sheet页,第" + (rowIndex + 1) + "行,产品名称为空 \r\n";
                                        // list.clear();
                                        // break loop;
                                    }
                                    break;
                                case 2:
                                    Double cell2 = cell.getNumericCellValue();
                                    if (null != cell2) {
                                        product.setStatus(Integer.valueOf(new DecimalFormat("#").format(cell2)));
                                    } else {
                                        errorMsg += "第" + (sheetIndex + 1) + "sheet页,第" + (rowIndex + 1) + "行,产品名称为空 \r\n";
                                        // list.clear();
                                        // break loop;
                                    }
                                    break;
                                case 3:
                                    BigDecimal cellV3 = setValue(cell, evaluator);
                                    if (null != cellV3) {
                                        product.setPrice(cellV3);
                                    } else {
                                        errorMsg += "第" + (sheetIndex + 1) + "sheet页,第" + (rowIndex + 1) + "行,产品名称为空 \r\n";
                                        // list.clear();
                                        // break loop;
                                    }
                                    break;
                                case 4:
                                    String cell4 = cell.getStringCellValue();
                                    if (StringUtils.isNotBlank(cell4)) {
                                        product.setDescription(cell4);
                                    } else {
                                        errorMsg += "第" + (sheetIndex + 1) + "sheet页,第" + (rowIndex + 1) + "行,产品名称为空 \r\n";
                                        // list.clear();
                                        // break loop;
                                    }
                                    break;
                                case 5:
                                    String cell5 = cell.getStringCellValue();
                                    if (StringUtils.isNotBlank(cell5)) {
                                        product.setCaption(cell5);
                                    } else {
                                        errorMsg += "第" + (sheetIndex + 1) + "sheet页,第" + (rowIndex + 1) + "行,产品名称为空 \r\n";
                                        // list.clear();
                                        // break loop;
                                    }
                                    break;
                                case 6:
                                    Double cellV6 = cell.getNumericCellValue();
                                    if (null != cellV6) {
                                        product.setStock(Integer.valueOf(new DecimalFormat("#").format(cellV6)));
                                    } else {
                                        errorMsg += "第" + (sheetIndex + 1) + "sheet页,第" + (rowIndex + 1) + "行,数量为空 \r\n";
                                        // list.clear();
                                        // break loop;
                                    }
                                    break;
                                default:
                                    break;
                            }
                        }else {
                            continue;
                        }
                    }
                    list.add(product);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            errorMsg += "格式有问题!请仔细检查!";
            list.clear();
            Map<String, Object> map = new HashMap<>();
            map.put("list", list);
            map.put("msg", errorMsg);
            return map;
        } finally {
            try {
                if (wb != null) {
                    wb.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            logger.debug(errorMsg);
        }
        Map<String, Object> map = new HashMap<>();
        map.put("list", list);
        map.put("msg", errorMsg);
        logger.debug(errorMsg);
        return map;
    }


    /**
     * 获取文件后缀
     *
     * @param oldName
     * @return
     */
    private static String getPostfix(String oldName) {
        if (StringUtils.isBlank(oldName) || !oldName.contains(".")) {
            return null;
        }
        return oldName.substring(oldName.lastIndexOf(".") + 1).trim();
    }

    /**
     * 获取文件前缀
     *
     * @param oldName
     * @return
     */
    private static String getPrefix(String oldName) {
        if (StringUtils.isBlank(oldName) || !oldName.contains(".")) {
            return null;
        }
        return oldName.substring(0, oldName.lastIndexOf(".")).trim();
    }

    /**
     * 获取文件名的月份
     *
     * @param oldName
     * @return
     */
    private static String getMonth(String oldName) {
        StringBuilder builder = new StringBuilder();
        if (StringUtils.isBlank(oldName) || !oldName.contains("月")) {
            return null;
        }
        String month = oldName.substring(0, oldName.lastIndexOf("月")).trim();
        if (month.length() == 1) {
            builder.append("0");
        }
        builder.append(month);
        return builder.toString();
    }

    /**
     * 2003 excel(xls)
     *
     * @param filePath
     * @return
     */
    private static boolean isExcel2003(String filePath) {
        return filePath.matches("^.+\\.(?i)(xls)$");
    }

    /**
     * 2007 excel(xlsx)
     *
     * @param filePath
     * @return
     */
    private static boolean isExcel2007(String filePath) {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }

    /**
     * 判断数值或公式
     *
     * @param cell
     * @param evaluator
     * @return
     */
    private static BigDecimal setValue(Cell cell, FormulaEvaluator evaluator) {
        BigDecimal bigDecimal = null;
        switch (cell.getCellTypeEnum()) {
            case NUMERIC:
                if (!(HSSFDateUtil.isCellDateFormatted(cell))) {
                    //数字
                    Double cellV1 = cell.getNumericCellValue();
                    if (null != cellV1) {
                        bigDecimal = new BigDecimal(String.valueOf(cellV1));
                    }
                }
                break;
            // 公式类型
            case FORMULA:
                //数字
                if (cell.getCachedFormulaResultType() == HSSFCell.CELL_TYPE_NUMERIC) {
                    Double cellV1 = cell.getNumericCellValue();
                    if (null != cellV1) {
                        bigDecimal = new BigDecimal(String.valueOf(cellV1));
                    }
                }
                break;
            default:
                break;
        }
        return bigDecimal;
    }
}
